Visual Basic 4 Database How-To/Visual Basic 4 Database - How-to (The Waite Group)(1995).iso/update.fr_/update.fr

Text File | 1995-07-04 | 6KB | 191 lines

VERSION 4.00
Begin VB.Form Form1
   BackColor       =   &H00C0C0C0&
   Caption         =   "UPDATEr"
   ClientHeight    =   3660
   ClientLeft      =   1095
   ClientTop       =   1485
   ClientWidth     =   5520
   BeginProperty Font
      name            =   "MS Sans Serif"
      charset         =   0
      weight          =   700
      size            =   8.25
      underline       =   0   'False
      italic          =   0   'False
      strikethrough   =   0   'False
   EndProperty
   Height          =   4065
   Left            =   1035
   LinkTopic       =   "Form1"
   ScaleHeight     =   3660
   ScaleWidth      =   5520
   Top             =   1140
   Width           =   5640
   Begin VB.CommandButton cmdClose
      Caption         =   "&Close"
      Height          =   495
      Left            =   3720
      TabIndex        =   3
      Top             =   2520
      Width           =   1215
   End
   Begin VB.CommandButton cmdRestore
      Caption         =   "&Restore"
      Height          =   495
      Left            =   2100
      TabIndex        =   2
      Top             =   2520
      Width           =   1215
   End
   Begin VB.CommandButton cmdUpdate
      Caption         =   "&Update"
      Height          =   495
      Left            =   540
      TabIndex        =   1
      Top             =   2520
      Width           =   1215
   End
   Begin VB.ListBox lstData
      BeginProperty Font
         name            =   "MS Sans Serif"
         charset         =   0
         weight          =   400
         size            =   8.25
         underline       =   0   'False
         italic          =   0   'False
         strikethrough   =   0   'False
      EndProperty
      Height          =   1590
      Left            =   540
      TabIndex        =   0
      Top             =   480
      Width           =   4395
   End
End
Attribute VB_Name = "Form1"
Attribute VB_Creatable = False
Attribute VB_Exposed = False
Option Explicit


Private Sub cmdUpdate_Click()
    Dim db As DATABASE
    Dim dbName As String
    Dim sql As String

    On Error GoTo UpdateError

  ' Get the database name and open the database.
    dbName = BiblioPath()       ' BiblioPath is a function in READINI.BAS
    Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)

    ' Build the SQL statement, beginning with the UPDATE clause, which
    ' designates the table to be modified.
    sql = "UPDATE Publishers"

    ' Set the new values of each field to be modified.
    sql = sql & " SET City = 'Indianapolis', Address = '201 W. 103rd St.',"
    sql = sql & " Zip = '46290'"

    ' Designate the records to be modified.
    sql = sql & " WHERE ([City] = 'Carmel')"
    sql = sql & " AND (Address LIKE '*11711*College*')"

    ' Execute the update query.
    db.Execute sql

    ' Show the modified records in the list box.
    ListRecords "Indianapolis"

Exit Sub
UpdateError:
    MsgBox Error$, vbExclamation
Exit Sub

End Sub

Private Sub cmdRestore_Click()
    Dim db As DATABASE
    Dim dbName As String
    Dim sql As String

    On Error GoTo RestoreError


   ' Get the database name and open the database.
    dbName = BiblioPath()       ' BiblioPath is a function in READINI.BAS
    Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)
    ' Build the SQL statement, beginning with the UPDATE clause, which
    ' designates the table to be modified.
    sql = "UPDATE Publishers"

    ' Set the new values of each field to be modified.
    sql = sql & " SET City = 'Carmel', Address = '11711 N. College Ave.',"
    sql = sql & " Zip = '46032'"

    ' Set the new values of each field to be modified.
    sql = sql & " WHERE ([City] = 'Indianapolis')"
    sql = sql & " AND (Address = '201 W. 103rd St.')"

    ' Execute the update query.
    db.Execute sql

    ' Execute the update query.
    ListRecords "Carmel"

Exit Sub
RestoreError:
    MsgBox Error$, vbExclamation
Exit Sub
End Sub

Private Sub ListRecords(cityName As String)
    Dim db As DATABASE
    Dim dbName As String
    Dim rs As Recordset
    Dim sql As String
    Dim streetAddress As String

    On Error GoTo ListError

    ' Set the correct street address based on the city name.
    If cityName = "Indianapolis" Then
        streetAddress = "201 W. 103rd St."
    Else
        streetAddress = "11711 N. College Ave."
    End If

    ' Get the database name and open the database.
    dbName = BiblioPath()       ' BiblioPath is a function in READINI.BAS
    Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)

    ' Create the recordset for the list box.
    sql = "SELECT [Company Name], [Address], [City], [State], [Zip]"
    sql = sql & " FROM Publishers"
    sql = sql & " WHERE [City] = '" & cityName & "'"
    sql = sql & " AND [Address] = '" & streetAddress & "'"
    Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

    lstData.Clear

    ' Show each record in the list box.
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do
            lstData.AddItem Left$(rs![Company Name], 10) _
                & ", " & rs![Address] & ", " & rs![City] _
                & ", " & rs![State] & " " & rs![Zip]
            rs.MoveNext
        Loop While Not rs.EOF
    End If
Exit Sub
ListError:
    MsgBox Error$, vbExclamation
Exit Sub
End Sub

Private Sub cmdClose_Click()
    End
End Sub